package com.bss.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.bss.utilities.DateUtilities;
import com.bss.beans.Room;
import com.bss.beans.RoomAllocation;
import com.bss.beans.RoomAndDomDetails;
import com.bss.beans.Dom;
import com.bss.framework.FrontController;


public class RoomDao {


	public static boolean AddNewBed(Dom dom) throws Exception
	{
		Connection conn;
		PreparedStatement Ps;
		
		String insertString="insert into doms(domId,domNo,roomId,domCharges) values(?,?,?,?)";
		
		conn=ConnectionDao.getConnection1();
		
		try{
			Ps=conn.prepareStatement(insertString);
			
			Ps.setInt(1, dom.getIDomId());
			Ps.setString(2, dom.getSDomNo());
			Ps.setInt(3, dom.getIRoomId());
			Ps.setInt(4, 10);
	
			Ps.executeUpdate();
		
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
			
		}
		
		return true;
	}
	

	public static boolean AddNewRoom(Room room) throws Exception
	{
		Connection conn;
		PreparedStatement Ps;
		
		String insertString="insert into rooms(roomId,roomNo,buildingName,floorNo,isDom,roomCharges) values(?,?,?,?,?,?)";
		
		conn=ConnectionDao.getConnection1();
		
		try{
			Ps=conn.prepareStatement(insertString);
			
			Ps.setInt(1, room.getIRoomId());
			Ps.setString(2, room.getSRoomNo());
			Ps.setString(3, room.getSBuildingName());
			Ps.setInt(4, room.getIFloorNo());
			Ps.setBoolean(5, room.getBIsDom());
			Ps.setInt(6, room.getICharges());
			
			Ps.executeUpdate();
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
			
		}
		
		return true;
	}
	
	public static int FindMaxDomId() throws Exception
	{
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		int patientId=0;
		
		String insertString="select max(domId) from doms";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				patientId=Rs.getInt(1);
			}
				
			return patientId;
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}	
	}
	
	public static int FindMaxRoomId() throws Exception
	{
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		int patientId=0;
		
		String insertString="select max(roomId) from rooms";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				patientId=Rs.getInt(1);
			}
				
			return patientId;
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}	
	}
	
	public static ArrayList<String> loadBuildingNames() throws Exception
	{
		ResultSet Rs=null;
		String user=null;
		ArrayList<String> userlist= new ArrayList<String>();
		String selectQuerry="select distinct(buildingName) from rooms";
		PreparedStatement Ps;
		Connection conn=ConnectionDao.getConnection1();
		
		try{
			
			Ps=conn.prepareStatement(selectQuerry);
			Rs=Ps.executeQuery();
			
			while(Rs.next()){
			
				user=Rs.getString(1);	
				userlist.add(user);
			}
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return userlist;
		
		
	}
	
	public static ArrayList<String> loadRoomNames(String buildingName) throws Exception
	{
		ResultSet Rs=null;
		String user=null;
		ArrayList<String> userlist= new ArrayList<String>();
		String selectQuerry="select distinct(roomNo) from rooms where buildingName='" +
				buildingName+"'";
		PreparedStatement Ps;
		Connection conn=ConnectionDao.getConnection1();
		
		try{
			
			Ps=conn.prepareStatement(selectQuerry);
			Rs=Ps.executeQuery();
			
			while(Rs.next()){
			
				user=Rs.getString(1);	
				userlist.add(user);
			}
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return userlist;
		
		
	}
	
	
	public static ArrayList<String> loadDomRoomNames(String buildingName) throws Exception
	{
		ResultSet Rs=null;
		String user=null;
		int roomId;
		String building=null;
		String selectQuerry;
		ArrayList<String> userlist= new ArrayList<String>();
		
		if(buildingName.compareTo("_")==0)
		{
			selectQuerry="select distinct(roomNo),roomId,buildingName from rooms where isdom=1";
		}
		else
		{
			selectQuerry="select distinct(roomNo),roomId from rooms where buildingName='" +
				buildingName+"' and isdom=1";
		}
		PreparedStatement Ps;
		Connection conn=ConnectionDao.getConnection1();
		
		try{
			
			Ps=conn.prepareStatement(selectQuerry);
			Rs=Ps.executeQuery();
			
			while(Rs.next()){
			
				user=Rs.getString(1);
				roomId=Rs.getInt(2);
				building=Rs.getString(3);
				if(building==null)
				{		
					userlist.add("NO Building"+":"+user+","+roomId);
				}
				else
				{
					if(building.length()==0)
					{
					userlist.add("NO Building"+":"+user+","+roomId);
					}
					else
					{
					userlist.add(building+":"+user+","+roomId);
					}
				}
			}
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return userlist;
		
		
	}
	
	
	public static ArrayList<String> loadDomNames(int roomId) throws Exception
	{
		ResultSet Rs=null;
		String user=null;
		ArrayList<String> userlist= new ArrayList<String>();
		String selectQuerry="select distinct(domNo) from doms where roomId=" +
				roomId;
		PreparedStatement Ps;
		Connection conn=ConnectionDao.getConnection1();
		
		try{
			
			Ps=conn.prepareStatement(selectQuerry);
			Rs=Ps.executeQuery();
			
			while(Rs.next()){
			
				user=Rs.getString(1);	
				userlist.add(user);
			}
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return userlist;
		
		
	}
	
	public static ArrayList<RoomAndDomDetails> loadAllDoms() throws Exception
	{
		
		ArrayList<RoomAndDomDetails> doms= new ArrayList<RoomAndDomDetails>();
		
		ResultSet Rs=null;
		String selectQuerry="select a.domId, a.domNo, a.roomId, a.domCharges, b.roomNo, b.buildingName, b.floorNo, a.isLower from doms a, rooms b where a.roomId=b.roomId ";
		PreparedStatement Ps;
		Connection conn=ConnectionDao.getConnection1();
		
try{
			
			Ps=conn.prepareStatement(selectQuerry);
			Rs=Ps.executeQuery();
	
			while(Rs.next()){
				RoomAndDomDetails dom=new RoomAndDomDetails();
				dom.setIDomId(Rs.getInt(1));
				dom.setSDomNo(Rs.getString(2));
				dom.setIRoomId(Rs.getInt(3));
				dom.setICharges(Rs.getInt(4));
				dom.setSRoomNo(Rs.getString(5));
				dom.setSBuildingName(Rs.getString(6));
				dom.setIFloorNo(Rs.getInt(7));
				dom.setLower(Rs.getBoolean(8));
				doms.add(dom);
			}
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return doms;
		
	}
	
	public static ArrayList<Integer> loadOccupiedDomId() throws Exception
	{
		ResultSet Rs=null;
		int dom;
		ArrayList<Integer> domlist= new ArrayList<Integer>();
		String selectQuerry="select domId from room_allocation where checkedOut is false";
		PreparedStatement Ps;
		Connection conn=ConnectionDao.getConnection1();
		
		try{
			
			Ps=conn.prepareStatement(selectQuerry);
			Rs=Ps.executeQuery();
			
			while(Rs.next()){
			
				dom=Rs.getInt(1);	
				domlist.add(dom);
			}
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return domlist;
		
		
	}
	
	
	public static ArrayList<RoomAndDomDetails> loadAllRooms() throws Exception
	{
		
		ArrayList<RoomAndDomDetails> doms= new ArrayList<RoomAndDomDetails>();
		
		ResultSet Rs=null;
		String selectQuerry="select roomId, roomCharges, roomNo, buildingName, floorNo from rooms where isDom is false";
		PreparedStatement Ps;
		Connection conn=ConnectionDao.getConnection1();
		
try{
			
			Ps=conn.prepareStatement(selectQuerry);
			Rs=Ps.executeQuery();
			
			while(Rs.next()){
			
				RoomAndDomDetails dom=new RoomAndDomDetails();
				dom.setIRoomId(Rs.getInt(1));
				dom.setICharges(Rs.getInt(2));
				dom.setSRoomNo(Rs.getString(3));
				dom.setSBuildingName(Rs.getString(4));
				dom.setIFloorNo(Rs.getInt(5));
	
				doms.add(dom);
			}
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return doms;
		
	}
	
	public static ArrayList<Integer> loadOccupiedRoomId() throws Exception
	{
		ResultSet Rs=null;
		int dom;
		ArrayList<Integer> domlist= new ArrayList<Integer>();
		String selectQuerry="select roomId from room_allocation where checkedOut is false";
		PreparedStatement Ps;
		Connection conn=ConnectionDao.getConnection1();
		
		try{
			
			Ps=conn.prepareStatement(selectQuerry);
			Rs=Ps.executeQuery();
			
			while(Rs.next()){
			
				dom=Rs.getInt(1);
				com.bss.framework.FrontController.trace("room id occupied"+dom);
				
				domlist.add(dom);
			}
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return domlist;
		
		
	}
	
	public static boolean allocateRoom(RoomAllocation roomAllocationDetails) throws Exception
	{
		Connection conn;
		PreparedStatement Ps;
		
		String insertString="insert into room_allocation (roomAllocationId,NoOfRooms,NoOfDoms,roomId,transactionId,checkedOut,dateofstay,amount) values(?,?,?,?,?,?,?,?)";
		
		conn=ConnectionDao.getConnection1();
		
		try{
			Ps=conn.prepareStatement(insertString);
			
			Ps.setInt(1, FindMaxRoomAllocationId()+1);
			Ps.setInt(2, 1);
			Ps.setInt(3, 0);
			Ps.setInt(4, roomAllocationDetails.getIRoomId());			
			Ps.setInt(5, roomAllocationDetails.getITransactionId());
			Ps.setBoolean(6, false);
			Ps.setTimestamp(7, DateUtilities.convertToTimestamp(roomAllocationDetails.getSDateOfStay()));
			Ps.setInt(8, roomAllocationDetails.getIAmount());
			
			Ps.executeUpdate();			
			// AUDIT
			FrontController.audit("Allocated Room, ID: "+ roomAllocationDetails.getITransactionId() + ", Amount: " + roomAllocationDetails.getIAmount() );
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
			
		}
		return true;
	}
	public static boolean allocateDom(RoomAllocation roomAllocationDetails) throws Exception
	{
		Connection conn;
		PreparedStatement Ps;
		
		String insertString="insert into room_allocation values(?,?,?,?,?,?,?,?,?,?)";
		
		conn=ConnectionDao.getConnection1();
		
		try{
			Ps=conn.prepareStatement(insertString);
			
			Ps.setInt(1, FindMaxRoomAllocationId()+1);
			Ps.setInt(2, 1);
			Ps.setInt(3, roomAllocationDetails.getINoOfDoms());
			Ps.setInt(4, roomAllocationDetails.getIRoomId());
			Ps.setInt(5, roomAllocationDetails.getIDomId());
			Ps.setInt(6, roomAllocationDetails.getITransactionId());
			Ps.setBoolean(7, false);
			Ps.setTimestamp(8, DateUtilities.convertToTimestamp(roomAllocationDetails.getSDateOfStay()));
			Ps.setInt(9, roomAllocationDetails.getIAmount());
			Ps.setTimestamp(10, null);
			Ps.executeUpdate();
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
			
		}
		return true;
		
	}
	
	
	public static int FindMaxRoomAllocationId() throws Exception
	{
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		int roomAllocationId=0;
		
		String insertString="select max(roomAllocationId) from room_allocation";
		
		conn=ConnectionDao.getConnection1();
		
		try
		{
			Ps=conn.prepareStatement(insertString);
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				roomAllocationId=Rs.getInt(1);
			}
				
			return roomAllocationId;
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}	
	}
	public static ArrayList<RoomAllocation> allocatedRoomsDoms(int transactionId) throws Exception
	{
		RoomAllocation roomDoms=null;
		ArrayList<RoomAllocation> list=new ArrayList<RoomAllocation>() ; 
		
		ResultSet Rs=null;
		String selectQuerry="select * from room_allocation where transactionId=? and checkedOut is false";
		PreparedStatement Ps;
		Connection conn=ConnectionDao.getConnection1();
		
		try{
			
			Ps=conn.prepareStatement(selectQuerry);
			Ps.setInt(1, transactionId);
			Rs=Ps.executeQuery();
			
			while(Rs.next()){
			
				roomDoms=new RoomAllocation();
				roomDoms.setIRoomId(Rs.getInt(4));
				roomDoms.setIDomId(Rs.getInt(5));
				roomDoms.setIAmount(Rs.getInt(9));
				list.add(roomDoms);
			}
			
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return list;
	}
	
	public static boolean deallocateAllRooms(int transactionId , String dateofcheckout) throws Exception
	{
		Connection conn;
		PreparedStatement Ps;
		conn=ConnectionDao.getConnection1();
		
		try
		{
			String strSelect = "update room_allocation set checkedOut=true,dateofcheckout=? where transactionId=? and checkedOut is false";
			Ps=conn.prepareStatement(strSelect);
			Ps.setTimestamp(1, DateUtilities.convertToTimestamp(dateofcheckout));
			Ps.setInt(2, transactionId);
			Ps.executeUpdate();
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		return true;
	}
	
	public static ArrayList<RoomAllocation> getPatientStay(int transactionId) throws Exception
	{
		ArrayList<RoomAllocation> allocation= new ArrayList<RoomAllocation>();
		Connection conn;
		PreparedStatement Ps;
		conn=ConnectionDao.getConnection1();
		ResultSet Rs=null;
		try
		{
			String strSelect = "select * from room_allocation where transactionId=?";
			
			Ps=conn.prepareStatement(strSelect);
			Ps.setInt(1, transactionId);
		
			
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				RoomAllocation details=new RoomAllocation();
				if(Rs.getTimestamp(10)!=null)
				{
					details.setSDateofcheckout(Rs.getTimestamp(10).toString());
				}
				details.setSDateOfStay(Rs.getTimestamp(8).toString());
				details.setIDomId(Rs.getInt(5));
				details.setIRoomId(Rs.getInt(4));
				details.setIAmount(Rs.getInt(9));
				
				allocation.add(details);
			}
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return allocation;
		
	}
	
	public static int getTransactionIdFromRoomId(int roomId) throws Exception
	{
		int transactionId=0;;
		Connection conn;
		PreparedStatement Ps;
		conn=ConnectionDao.getConnection1();
		ResultSet Rs=null;
		try
		{
			String strSelect = "select transactionId from room_allocation where roomAllocationId=(select max(roomAllocationId) from room_allocation where checkedOut=false && roomId=?)";
			
			Ps=conn.prepareStatement(strSelect);
			Ps.setInt(1, roomId);
		
			
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				transactionId=Rs.getInt(1);
			}
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		
		return transactionId;
	}
	
	public static int getTransactionIdFromDomId(int domId) throws Exception
	{
		int transactionId=0;;
		Connection conn;
		PreparedStatement Ps;
		conn=ConnectionDao.getConnection1();
		ResultSet Rs=null;
		try
		{
			String strSelect = "select transactionId from room_allocation where roomAllocationId=(select max(roomAllocationId) from room_allocation where domId=? && checkedOut=false)";
			
			Ps=conn.prepareStatement(strSelect);
			Ps.setInt(1, domId);
		
			
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				transactionId=Rs.getInt(1);
			}
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		
		
		return transactionId;
	}
	
	public static boolean temporaryCheckOut(int transactionId, int domId) throws Exception
	{
		Connection conn;
		PreparedStatement Ps;
		
		String insertString="insert into tempcheckout(transactionId, domId, checkedOut) values(?,?,?)";
		
		conn=ConnectionDao.getConnection1();
		
		try{
			Ps=conn.prepareStatement(insertString);
			
			Ps.setInt(1, transactionId);
			Ps.setInt(2, domId);
			Ps.setBoolean(3, true);
	
			Ps.executeUpdate();
		
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
			
		}
		
		return true;
	}
	
	public static ArrayList<Integer> getTempCheckOutDoms(int transactionId) throws Exception
	{
		ArrayList<Integer> doms= new ArrayList<Integer>();
		Connection conn;
		PreparedStatement Ps;
		conn=ConnectionDao.getConnection1();
		ResultSet Rs=null;
		try
		{
			String strSelect = "select distinct(domId) from tempcheckout where transactionId=? and checkedOut is true";
			
			Ps=conn.prepareStatement(strSelect);
			Ps.setInt(1, transactionId);
		
			
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				doms.add(Rs.getInt(1));
			}
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return doms;
		
	}
	 
	public static boolean tempCheckIn(int transactionId ) throws Exception
	{
		Connection conn;
		PreparedStatement Ps;
		conn=ConnectionDao.getConnection1();
		int count;
		try
		{
			String strSelect = "update tempcheckout set checkedOut = false where transactionId=? ";
			Ps=conn.prepareStatement(strSelect);
			Ps.setInt(1, transactionId);
			count = Ps.executeUpdate();
			
			com.bss.framework.FrontController.trace("Row Count:"+count);
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		if(count == 0)
		{
			return false;
		}
		
		return true;
	}
	
	
	public static ArrayList<Integer> getTempCheckOutDoms() throws Exception
	{
		ArrayList<Integer> doms= new ArrayList<Integer>();
		Connection conn;
		PreparedStatement Ps;
		conn=ConnectionDao.getConnection1();
		ResultSet Rs=null;
		try
		{
			String strSelect = "select distinct(domId) from tempcheckout where  checkedOut is true";
			
			Ps=conn.prepareStatement(strSelect);
			
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				doms.add(Rs.getInt(1));
			}
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		return doms;
		
	}
	
	
	public static int getRoomIdFromTransactionId(int transactionId) throws Exception
	{
		int roomId = 0;
		Connection conn;
		PreparedStatement Ps;
		conn=ConnectionDao.getConnection1();
		ResultSet Rs=null;
		try
		{
			String strSelect = "select roomId from room_allocation where transactionId = ? and roomId is not null and dateofcheckout is null";
			
			Ps=conn.prepareStatement(strSelect);
			Ps.setInt(1, transactionId);
		
			
			Rs=Ps.executeQuery();
			
			while(Rs.next())
			{
				roomId=Rs.getInt(1);
				
			}
		}
		finally
		{
			ConnectionDao.closeConnection(conn);
		}
		
		com.bss.framework.FrontController.trace("RoomID:"+roomId);
		return roomId;
	}
	
	 public static int findDomAmount(int transactionId)throws Exception
	 {
		 int amount = 0 ;
		 Connection conn;
			PreparedStatement Ps;
			conn=ConnectionDao.getConnection1();
			ResultSet Rs=null;
			try
			{
				String strSelect = "select max(amount) from room_allocation where transactionId = ? and domId is not null and dateofcheckout is null";
				
				Ps=conn.prepareStatement(strSelect);
				Ps.setInt(1, transactionId);
			
				
				Rs=Ps.executeQuery();
				
				while(Rs.next())
				{
					amount=Rs.getInt(1);
					
				}
			}
			finally
			{
				ConnectionDao.closeConnection(conn);
			}
			
		
		 
		 return amount;
	 }
	 
	 public static int findRoomAmount(int transactionId)throws Exception
	 {
		 int amount = 0 ;
		 Connection conn;
			PreparedStatement Ps;
			conn=ConnectionDao.getConnection1();
			ResultSet Rs=null;
			try
			{
				String strSelect = "select max(amount) from room_allocation where transactionId = ? and roomId is not null and dateofcheckout is null and domId is null";
				
				Ps=conn.prepareStatement(strSelect);
				Ps.setInt(1, transactionId);
			
				
				Rs=Ps.executeQuery();
				
				while(Rs.next())
				{
					amount=Rs.getInt(1);
					
				}
			}
			finally
			{
				ConnectionDao.closeConnection(conn);
			}
			
		
		 
		 return amount;
	 }

}


